package com.ftwj.demo.base.wrapper;

import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.ftwj.demo.base.constant.Const;
import com.ftwj.demo.base.constant.QueryModel;

import cn.hutool.core.util.StrUtil;
/**
*
* <p>Title: ConditionWrapper</p>  
* <p>Description: SQL条件构建器</p>
* <p>Copyright: Copyright (c) 2020 </p>  
* <p>Company: 兰州飞天网景信息产业有限公司</p>    
* @author WangSLi
* @date 2020年3月25日 下午6:20:42
* @version V1.0
 */
//TODO 持续更新中...
public class ConditionWrapper implements Condition{

	private StringBuilder builder;
	protected LinkedList<Object> parameter;
	
	public ConditionWrapper() {
		builder=new StringBuilder();
		parameter=new LinkedList<Object>();
	}
	public StringBuilder getBuilder() {
		return builder;
	}
	public LinkedList<Object> getParameter() {
		return parameter;
	}

	/**
	 * OR
	 */
	public ConditionWrapper and() {
		builder.append(Const.CONNECTOR_AND);
		return this;
	}
	/**
	 * AND
	 */
	public ConditionWrapper or() {
		builder.append(Const.CONNECTOR_OR);
		return this;
	}
	
	/**
	 *	构造条件"="
	 */
	@Override
	public ConditionWrapper eq(String column, Object val) {
		validate(column);
		String key=StrUtil.format("`{}`", column);
		String str=null==val?(key+Const.CONDITION_ISNULL):(key+QueryModel.EQUALS);
		if(null!=val) {parameter.add(val);}
		builder.append(str);
		return this;
	}
	/**
	 *	构造条件"<>"
	 */
	@Override
	public ConditionWrapper ne(String column, Object val) {
		validate(column);
		String key=StrUtil.format("`{}`", column);
		String str=null==val?(key+Const.CONDITION_ISNOTNULL):(key+QueryModel.UNEQUAL_TO);
		if(null!=val) {parameter.add(val);}
		builder.append(str);
		return this;
	}
	/**
	 *	构造条件大于" >"
	 */
	@Override
	public ConditionWrapper gt(String column,Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.GREATER_THAN);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件大于等于 ">="
	 */
	@Override
	public ConditionWrapper ge(String column,Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.GREATER_THAN_EQUALS);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件小于"<"
	 */
	@Override
	public ConditionWrapper lt(String column,Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.LESS_THAN);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件小于等于 "<="
	 */
	@Override
	public ConditionWrapper le(String column,Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.LESS_THAN_EQUALS);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件全部字段"="或"IS NULL"
	 */
	@Override
	public ConditionWrapper allEq(Map<String, Object> params) {
		List<String> str=new LinkedList<>();
		for(Map.Entry<String, Object> entry : params.entrySet()) {
			validate(entry.getKey());
			String key=StrUtil.format("`{}`", entry.getKey());
			Object value=entry.getValue();
			StringBuilder builder=new StringBuilder(key);
			if(null!=value) {
				builder.append(QueryModel.EQUALS);
				parameter.add(value);
			}else {
				builder.append(Const.CONDITION_ISNULL);
			}
			str.add(builder.toString());
		}
		builder.append(StrUtil.join(Const.CONNECTOR_AND, str.toArray()));
		return this;
	}
	/**
	 *	构造条件BETWEEN 值1 AND 值2
	 */
	@Override
	public ConditionWrapper between(String column, Object valo, Object valt) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.BETWEEN_AND);
		parameter.addAll(Arrays.asList(valo,valt));
		return this;
	}
	/**
	 *	构造条件 NOT BETWEEN 值1 AND 值2
	 */
	@Override
	public ConditionWrapper notBetween(String column, Object valo, Object valt) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.NOT_BETWEEN_AND);
		parameter.addAll(Arrays.asList(valo,valt));
		return this;
	}
	/**
	 *	构造条件 LIKE '%值%'
	 */
	@Override
	public ConditionWrapper like(String column, Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.ALL_LIKE);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件NOT LIKE '%值%'
	 */
	@Override
	public ConditionWrapper notLike(String column, Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.NOT_LIKE_CONCAT);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件 LIKE '值%'
	 */
	@Override
	public ConditionWrapper likeLeft(String column, Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.LEFT_LIKE);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件 LIKE '%值'
	 */
	@Override
	public ConditionWrapper likeRight(String column, Object val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+QueryModel.RIGHT_LIKE);
		parameter.add(val);
		return this;
	}
	/**
	 *	构造条件 字段 IS NULL
	 */
	@Override
	public ConditionWrapper isNull(String column) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+Const.CONDITION_ISNULL); 
		return this;
	}
	/**
	 *	构造条件 字段 IS NOT NULL
	 */
	@Override
	public ConditionWrapper isNotNull(String column) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+Const.CONDITION_ISNOTNULL); 
		return this;
	}
	/**
	 *	构造条件 字段 IN (?, ?, ...)
	 */
	
	public ConditionWrapper in(String column,Object... val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+" IN (");
		StrUtil.repeatAndJoin("?", val.length, ",");
		builder.append(") ");
		parameter.addAll(Arrays.asList(val));
		return this;
	}
	
	/**
	 *	构造条件 字段 NOT IN (?, ?, ...)
	 */
	@Override
	public ConditionWrapper notIn(String column,Object... val) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+" NOT IN (");
		StrUtil.repeatAndJoin("?", val.length, ",");
		builder.append(") ");
		parameter.addAll(Arrays.asList(val));
		return this;
	}
	
	/**
	 *	构造条件 字段 IN ( sql语句 )
	 */
	@Override
	public ConditionWrapper inSql(String column,String sql) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+" IN (?)");
		parameter.addAll(Arrays.asList(sql));
		return this;
	}
	/**
	 *	构造条件 字段 NOT IN ( sql语句 )
	 */
	@Override
	public ConditionWrapper notInSql(String column,String sql) {
		validate(column);
		builder.append(StrUtil.format("`{}`", column)+" NOT IN (?)");
		parameter.addAll(Arrays.asList(sql));
		return this;
	}
	/**
	 *	构造条件 分组：GROUP BY 字段, ...
	 */
	@Override
	public ConditionWrapper groupBy(String... columns) {
		validate(columns);
		List<String> orders=Arrays.asList(columns).stream().map(str->StrUtil.format("`{}`",str)).collect(Collectors.toList());
		if(builder.indexOf("GROUP BY")!=-1) {
			builder.append(","+StrUtil.join(" , ",orders)); 
		}else {
			builder.append(" GROUP BY "+StrUtil.join(" , ",orders)); 
		}
		return this;
	}
	/**
	 *	构造条件 排序ASC：ORDER BY 字段 ,字段1 ...
	 */
	@Override
	public ConditionWrapper orderByAsc(String... columns) {
		validate(columns);
		List<String> orders=Arrays.asList(columns).stream().map(str-> StrUtil.appendIfMissingIgnoreCase(StrUtil.format("`{}`",str), " ASC")).collect(Collectors.toList());
		if(builder.indexOf("ORDER BY")!=-1) {
			builder.append(","+StrUtil.join(" , ",orders)); 
		}else {
			builder.append(" ORDER BY "+StrUtil.join(" , ",orders)); 
		}
		return this;
	}
	/**
	 *	构造条件 排序DESC：ORDER BY 字段 ,字段1 ...
	 */
	@Override
	public ConditionWrapper orderByDesc(String... columns) {
		validate(columns);
		List<String> orders=Arrays.asList(columns).stream().map(str->StrUtil.appendIfMissingIgnoreCase(StrUtil.format("`{}`",str), " DESC")).collect(Collectors.toList());
		if(builder.indexOf("ORDER BY")!=-1) {
			builder.append(","+StrUtil.join(" , ",orders)); 
		}else {
			builder.append(" ORDER BY "+StrUtil.join(" , ",orders)); 
		}
		return this;
	}
	/**
	 *	构造条件 排序：ORDER BY 字段 排序方式,字段1 排序方式...
	 */
	@Override
	public ConditionWrapper orderBy(String... columns) {
		validate(columns);
		List<String> orders=Arrays.asList(columns).stream().map(str->StrUtil.format("`{}`",str)).collect(Collectors.toList());
		if(builder.indexOf("ORDER BY")!=-1) {
			builder.append(","+StrUtil.join(" , ",orders)); 
		}else {
			builder.append(" ORDER BY "+StrUtil.join(" , ",orders)); 
		}
		return this;
	}
	/**
	 *	构造条件 having：HAVING ("sum(age) > 10")
	 */
	@Override
	public ConditionWrapper having(String... params) {
		validate(params);
		List<String> orders=Arrays.asList(params).stream().map(str-> StrUtil.format("`{}`",str)).collect(Collectors.toList());
		builder.append(" HAVING ( "+StrUtil.join(" AND ",orders)+" )"); 
		return this;
	}
	/**
	 * 
	 */
	@Override
	public ConditionWrapper applySql(String sql, Object... params) {
		builder.append(sql);
		parameter.addAll(Arrays.asList(params));
		return this;
	}
	/**
	 * 
	 * <p>Title: validateSql</p>  
	 * <p>Description: 判断是否存在SQL注入风险</p>  
	 * @param column
	 * @return 
	 * @author WangSLi
	 * @date 2020年3月25日  
	 * @version 1.0
	 */
	private static final String BAD_SQL = "and|exec|execute|insert|select|delete|update|count|drop|chr|mid|master|truncate|" +
             					   "char|declare|sitename|net|xp_cmdshell|or|like|and|exec|execute|create|drop|" +
             					   "table|from|grant|use|group_concat|information_schema.columns|table_schema|union|" +
             					   "where|order by|chr|asc|desc|/|#|;|-|+|,|%|'|&|*";
    
    protected void  validate(String... columns) {
    	String[] badStrs = BAD_SQL.split("\\|");
        List<String> keyword=Arrays.asList(badStrs);
        for (int i = 0; i < columns.length; i++) {
        	if(keyword.contains(columns[i].toLowerCase())) {
				throw new RuntimeException(StrUtil.format("【存在非法字符】：{}", columns[i]));
			}
		}
    }
	
}
